﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using DigDes.DSchool.SUPS.DataAccess.Interface;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class WebFormDBLoader
    {

        private static readonly string ConnectionStringKey = "SUPSDB";

        public CarInfo GetCarFromReader(SqlDataReader reader)
        {
            CarInfo car = new CarInfo();

            car.CarNumber = (String)reader["carNumber"];
            car.CargoWeight = (String)reader["cargoWeight"];
            if (reader["Station1"] != DBNull.Value)
                car.DestinationStation = (String)reader["Station1"];
            else
                car.DestinationStation = "Нет";
            if (reader["cargoName"] != DBNull.Value)
                car.CargoCode = (String)reader["cargoName"];
            else
                car.CargoCode = "Нет";
            car.ConsigneeCode = (String)reader["consigneeCode"];
            car.CarParkType = (String)reader["carParkType"];
            if (reader["Station2"] != DBNull.Value)
                car.DepartureStation = (String)reader["Station2"];
            else
                car.DepartureStation = "Нет";
            car.OperationCode = (String)reader["operationName"];
            car.StringOperationTime = reader["operationTime"].ToString();
            if (reader["Station3"] != DBNull.Value)
                car.StationOfOperation = (String)reader["Station3"];
            else
                car.StationOfOperation = "Нет";
            car.DeliveryRoad = (String)reader["Road1"];
            car.ReceipRoad = (String)reader["Road2"];
            car.TrainIndex = (String)reader["trainIndex"];
            car.TrainNumber = (String)reader["trainNumber"];
            return car;
        }

        public List<CarInfo> GetAllCarsInfo()
        {


            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {

                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "select carNumber, cargoWeight,S1.Name as Station1,Cargo.Name as cargoName,consigneeCode,carParkType,S2.Name as Station2,CarOperation.Name as operationName,operationTime,S3.Name as Station3,R1.Name as Road1,R2.Name as Road2,trainIndex,trainNumber from SomeTable left outer join Station as S1 on S1.Code = destinationStation left outer join Cargo on Cargo.ShortCode = cargoCode left outer join Station as S2 on S2.Code = departureStation left outer join CarOperation on CarOperation.Code = operationCode left outer join Station as S3 on S3.Code = stationOfOperation left outer join Road as R1 on R1.Code = deliveryRoad left outer join Road as R2 on R2.Code = receipRoad ORDER BY carNumber,Month(operationTime),DAY(operationTime),DatePart(HOUR, operationTime),DatePart(MINUTE, operationTime)";


                    List<CarInfo> cars = new List<CarInfo>();
                    sqlConn.Open();
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            cars.Add(GetCarFromReader(reader));
                        }

                        if (reader != null)
                        { reader.Close(); }
                        sqlConn.Close();

                    }
                    return cars;
                }
            }

        }
    }
}

